package smoker.db;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import smoker.tools.*;

public class DBCenter implements IDBCenter {
	private Connection conn = ConnectionManager.CreateConnection();
	private CallableStatement cst;
	private ResultSet rs;
	private final String FILENAME = "src/smoker/db/ServerConfig.properties";
	private Properties p;

	public DBCenter() {
		p = new Properties();
	}
	
	public Vector<Department> getDepartments() {
		Vector<Department> DepartmentList = new Vector<Department>();
		String sqlcmd = "{ call proc_getDepartments }";
		try {
			cst = conn.prepareCall(sqlcmd);
			rs = cst.executeQuery();
			while (rs.next()) {
				Department dpt = new Department();
				dpt.setDptID(rs.getInt("DptID"));
				dpt.setDptName(rs.getString("DptName"));
				dpt.setDptMemo(rs.getString("DptMemo"));
				DepartmentList.add(dpt);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return DepartmentList;
	}

	public Vector<User> getAllUser() {
		return null;
	}

	public User loginValidate(User user) {
		String loginID = user.getLoginID();
		String password = user.getPassword();
		String sqlcmd = "{ call proc_LoginValidate(?, ?) }";
		try {
			cst = conn.prepareCall(sqlcmd);
			cst.setString(1, loginID);
			cst.setString(2, password);
			rs = cst.executeQuery();
			while (rs.next()) {
				User userFullInfo = new User();
				userFullInfo.setLoginID(rs.getString("LoginID"));
				userFullInfo.setPassword(rs.getString("Password"));
				userFullInfo.setNickName(rs.getString("NickName"));
				userFullInfo.setName(rs.getString("Name"));
				userFullInfo.setSex(rs.getString("Sex"));
				userFullInfo.setBirthday(rs.getString("Birthday"));
				userFullInfo.setAddress(rs.getString("Address"));
				userFullInfo.setEmail(rs.getString("Email"));
				userFullInfo.setTelphone(rs.getString("Phone"));
				userFullInfo.setMemo(rs.getString("Memo"));
				userFullInfo.setDepartmentName(rs.getString("DptName"));
				userFullInfo.setIcon(new ImageIcon(rs.getString("UserPhoto")));
				return userFullInfo;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public int delUser(String loginID) {
		int returnValue = -1;
		String sqlcmd = "{ ? = call proc_DelUser(?) }";
		try {
			cst = conn.prepareCall(sqlcmd);
			cst.registerOutParameter(1, Types.INTEGER);
			cst.setString(2, loginID);
			cst.executeUpdate();
			returnValue = cst.getInt(1);
		}catch(Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}
	
	public int addUser(User addUser) {
		int returnValue = -1;
		String sqlcmd = "{ ? = call proc_AddUser(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }";
		try {
			cst = conn.prepareCall(sqlcmd);
			cst.registerOutParameter(1, Types.INTEGER);
			cst.setString(2, addUser.getLoginID());
			cst.setString(3, addUser.getPassword());
			cst.setString(4, addUser.getNickName());
			cst.setString(5, addUser.getName());
			cst.setString(6, addUser.getTelphone());
			cst.setString(7, addUser.getSex());
			cst.setString(8, addUser.getBirthday());
			cst.setString(9, addUser.getEmail());
			cst.setString(10, addUser.getAddress());
			cst.setString(11, addUser.getDepartmentName());
			cst.setString(12, addUser.getIcon().toString());
			cst.setString(13, addUser.getMemo());
			cst.executeUpdate();
			returnValue = cst.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}
	
	public int updateUser(User editUser) {
		int returnValue = -1;
		String sqlcmd = "{ ? = call proc_UpdateUser(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }";
		try {
			cst = conn.prepareCall(sqlcmd);
			cst.registerOutParameter(1, Types.INTEGER);
			cst.setString(2, editUser.getLoginID());
			cst.setString(3, editUser.getPassword());
			cst.setString(4, editUser.getNickName());
			cst.setString(5, editUser.getName());
			cst.setString(6, editUser.getTelphone());
			cst.setString(7, editUser.getSex());
			cst.setString(8, editUser.getBirthday());
			cst.setString(9, editUser.getEmail());
			cst.setString(10, editUser.getAddress());
			cst.setString(11, editUser.getDepartmentName());
			cst.setString(12, editUser.getIcon().toString());
			cst.setString(13, editUser.getMemo());
			cst.executeUpdate();
			returnValue = cst.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}
	
	public int addDpt(Department addDptInfo) {
		String sqlcmd = "{? = call proc_addDept(?,?)}";
		int returnValue = 0;
		try {
			cst = conn.prepareCall(sqlcmd);
			cst.registerOutParameter(1, Types.INTEGER);
			cst.setString(2, addDptInfo.getDptName());
			cst.setString(3, addDptInfo.getDptMemo());
			cst.executeUpdate();
			returnValue = cst.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return returnValue;
	}
	
	public int delDpt(String dptName){
		String sqlcmd = "{? = call proc_delDept(?)}";
		int returnValue = 0;
		try {
			cst = conn.prepareCall(sqlcmd);
			cst.registerOutParameter(1, Types.INTEGER);
			cst.setString(2, dptName);
			cst.executeUpdate();
			returnValue = cst.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return returnValue;
	}


	public int upDateDpt(Department editDptInfo) {
		String sqlcmd = "{? = call proc_upDateDept(?,?)}";
		int returnValue = 0;
		try {
			cst = conn.prepareCall(sqlcmd);
			cst.registerOutParameter(1, Types.INTEGER);
			cst.setString(2, editDptInfo.getDptName());
			cst.setString(3, editDptInfo.getDptMemo());
			cst.executeUpdate();
			returnValue = cst.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return returnValue;
	}
	
	public  Company getCompayInfo() {
		Company com = new Company();;	
		String sqlcmd = "{ call proc_getCompanyInfo }";
			try {
				cst = conn.prepareCall(sqlcmd);
				rs = cst.executeQuery();
				rs.next();
				com.setCompanyName(rs.getString("companyName"));
				com.setCompanyMemo(rs.getString("companyMemo"));
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return com;
	}


	public int upDateCompany(Company editComInfo) {
		String sqlcmd = "{? = call proc_upDateCompany(?,?)}";
		int returnValue = 0;
		try {
			cst = conn.prepareCall(sqlcmd);
			cst.registerOutParameter(1, Types.INTEGER);
			cst.setString(2, editComInfo.getCompanyName());
			cst.setString(3, editComInfo.getCompanyMemo());
			cst.executeUpdate();
			returnValue = cst.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return returnValue;
	}
	
	public int getPort(){
		try {
			InputStream is = new FileInputStream(FILENAME);
			p.load(is);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return Integer.parseInt(p.getProperty("ServerPort"));
	}
	
	public void setPort(String data) {
		try {
			OutputStream os = new FileOutputStream(FILENAME);
			p.setProperty("ServerPort", data);
			p.store(os, "");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}

	public User queryUser(String loginId) {
		String sqlcmd = "{ call proc_queryUser(?) }";
		try {
			cst = conn.prepareCall(sqlcmd);
			cst.setString(1, loginId);
			rs = cst.executeQuery();
			while (rs.next()) {
				User userFullInfo = new User();
				userFullInfo.setLoginID(rs.getString("LoginID"));
				userFullInfo.setPassword(rs.getString("Password"));
				userFullInfo.setNickName(rs.getString("NickName"));
				userFullInfo.setName(rs.getString("Name"));
				userFullInfo.setSex(rs.getString("Sex"));
				userFullInfo.setBirthday(rs.getString("Birthday"));
				userFullInfo.setAddress(rs.getString("Address"));
				userFullInfo.setEmail(rs.getString("Email"));
				userFullInfo.setTelphone(rs.getString("Phone"));
				userFullInfo.setMemo(rs.getString("Memo"));
				userFullInfo.setDepartmentName(rs.getString("DptName"));
				userFullInfo.setIcon(new ImageIcon(rs.getString("UserPhoto")));
				return userFullInfo;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
